﻿Imports System.Data.SqlClient
Public Class frmQualifications
    Dim CN As New SqlConnection
    Dim strCoursesID As String
    Dim intID As Integer
    Private Sub frmQualifications_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        CN.ConnectionString = My.Settings.IT308ConnectionString
        Load_cboTeachers()
    End Sub
    Private Sub Load_cboTeachers()
        'WB - Creates the connection to the Database to load Teachers names into cboCourses
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT FName, LName, Department FROM TEACHER ORDER BY LName"
        Dim Reader As SqlDataReader

        'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
        Try
            CN.Open()
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
        End Try

        'WB - Reads Ids and names from the TEACHER table and enters them into cboTeacher after clearing
        cboTeachers.Items.Clear()
        Reader = cmd.ExecuteReader
        While Reader.Read
            cboTeachers.Items.Add(Convert.ToString(Reader.Item("LName")).Trim & ", " & Convert.ToString(Reader.Item("FName")).Trim & " - - " & Convert.ToString(Reader.Item("Department")).Trim)
        End While
        'WB - Closes connection
        If CN.State = ConnectionState.Open Then CN.Close()
    End Sub
    Private Sub Load_lstAllCourses()
        'WB - Creates the command to load course IDs and names into lstAllCourses
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT ID, Name FROM COURSES"
        Dim Reader As SqlDataReader

        'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
        Try
            CN.Open()
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
            Me.Close()
        End Try

        'WB - Reads IDs and Names from the COURSES Table and enters them into lstAllCourses after clearing
        Reader = cmd.ExecuteReader
        lstAllCourses.Items.Clear()
        While Reader.Read
            lstAllCourses.Items.Add(Convert.ToString(Reader.Item("ID")).Trim & " " & Convert.ToString(Reader.Item("Name")).Trim)
        End While

        'WB - Closes connection with the database if it is still open
        If CN.State = ConnectionState.Open Then CN.Close()

        'WB - removes the items from lstAllCourses that are already in lstPrerequisits.
        For Each Item In lstQualifications.Items()
            lstAllCourses.Items.Remove(Item)
        Next

    End Sub
    Private Sub Load_lstQualifications()
        'WB - Creates the command to load courseIDs into lstQualifications
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT COURSES.ID, COURSES.Name From COURSES INNER JOIN QUALIFICATIONS ON COURSES.ID = QUALIFICATIONS.CoursesID WHERE QUALIFICATIONS.TeacherID= @TeacherID"
        Dim Reader As SqlDataReader

        'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
        Try
            CN.Open()
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
            Me.Close()
        End Try

        'WB - Reads IDs from the COURSES Table and enters them into lstPrerequisits after clearing
        cmd.Parameters.AddWithValue("@TeacherID", intID)
        Reader = cmd.ExecuteReader
        lstQualifications.Items.Clear()
        While Reader.Read
            lstQualifications.Items.Add(Convert.ToString(Reader.Item("ID")).Trim & " " & Convert.ToString(Reader.Item("Name")).Trim)
        End While

        'WB - Closes connection with the database if it is still open
        If CN.State = ConnectionState.Open Then CN.Close()
    End Sub
    Private Sub cboTeachers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboTeachers.SelectedIndexChanged
        If cboTeachers.SelectedIndex = -1 Then
            MessageBox.Show("Please Select a Course to Procede", "Make a Selection")
        Else
            'WB - First gets the Program ID and stores it into a Global Vaialble intID
            Dim cmd As New SqlCommand
            cmd.Connection = CN
            cmd.CommandText = "SELECT ID FROM TEACHER WHERE FName = @FName AND LName = @LName"
            Dim Reader As SqlDataReader
            'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
            Try
                CN.Open()
            Catch ex As Exception
                MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
            End Try

            'WB - Reads ProgramID from the PROGRAM Table 
            cmd.Parameters.AddWithValue("@LName", cboTeachers.SelectedItem.ToString.Substring(0, cboTeachers.SelectedItem.ToString.IndexOf(",")).Trim)
            cmd.Parameters.AddWithValue("@FName", cboTeachers.SelectedItem.ToString.Substring(cboTeachers.SelectedItem.ToString.IndexOf(" ") + 1, (cboTeachers.SelectedItem.ToString.IndexOf("-") - cboTeachers.SelectedItem.ToString.IndexOf(" ")) - 2).Trim)
            Reader = cmd.ExecuteReader
            While Reader.Read
                intID = Convert.ToInt32(Reader.Item("ID"))
            End While

            'WB - Closes connection with the database if it is still open
            If CN.State = ConnectionState.Open Then CN.Close()
            Load_lstQualifications()
            Load_lstAllCourses()
        End If
    End Sub

    Private Sub btnAddCourse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddCourse.Click
        'WB - Verify that an item has been selected to add.
        If lstAllCourses.SelectedIndex = -1 Then
            MessageBox.Show("No Course Selected to Add", "Error")
        Else
            'WB - declare and instantiate neccesary variables to connect to Database
            Dim intRows As Integer
            Dim cmd As New SqlCommand
            cmd.Connection = CN

            'WB - Tries to connect to the database - if unsuccessfull it reports error and closes program
            Try
                CN.Open()
            Catch ex As Exception
                MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
            End Try

            'WB - Sets up the New Command to enter the Record into Qualifications
            Try
                cmd.CommandText = "INSERT QUALIFICATIONS (TeacherID, CoursesID, Rating) VALUES (@TeacherID, @CoursesID, @Rating)"
                cmd.Parameters.AddWithValue("@TeacherID", intID)
                cmd.Parameters.AddWithValue("@CoursesID", lstAllCourses.SelectedItem.ToString.Substring(0, lstAllCourses.SelectedItem.ToString.IndexOf(" ")))
                cmd.Parameters.AddWithValue("@Rating", 100)
                'WB - Performs the Execution
                intRows = cmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox("Error adding Qualification to " & cboTeachers.SelectedItem.ToString & "- " & ex.Message)
            End Try

            'WB - Reports to the user if the add statement failed
            If intRows = 0 Then
                MsgBox("Error adding Qualification to " & cboTeachers.SelectedItem.ToString)
            End If

            'WB - Closes connection with the database if it is still open
            If CN.State = ConnectionState.Open Then CN.Close()
            'WB - refreshes the list boxes
            lstQualifications.Items.Add(lstAllCourses.SelectedItem)
            lstAllCourses.Items.Remove(lstAllCourses.SelectedItem)
        End If
    End Sub

    Private Sub btnRemoveCourse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRemoveCourse.Click
        'WB - Verify that an item has been selected to remove.
        If lstQualifications.SelectedIndex = -1 Then
            MessageBox.Show("No Course Selected to Remove", "Error")
        Else
            'WB - declare and instantiate neccesary variables to connect to Database
            Dim cmd As New SqlCommand
            cmd.Connection = CN
            Dim intRows As Integer

            'WB - Tries to connect to the database - if unsuccessfull it reports error and closes program
            Try
                CN.Open()
            Catch ex As Exception
                MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
            End Try

            'WB - Sets up the New Command to enter the Record into PROGRAMCOURSES
            Try
                cmd.CommandText = "DELETE QUALIFICATIONS WHERE CoursesID = @CoursesID AND TeacherID = @TeacherID"
                cmd.Parameters.AddWithValue("@CoursesID", lstQualifications.SelectedItem.ToString.Substring(0, lstQualifications.SelectedItem.ToString.IndexOf(" ")))
                cmd.Parameters.AddWithValue("@TeacherID", intID)

                'WB - Performs the Execution
                intRows = cmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox("Error Removing Qualification from " & cboTeachers.SelectedItem.ToString & " - " & ex.Message)
            End Try

            'WB - Reports to the user if the add statement failed
            If intRows = 0 Then
                MsgBox("Error Removing Qualification from " & cboTeachers.SelectedItem.ToString)
            End If

            'WB - Closes connection with the database if it is still open
            If CN.State = ConnectionState.Open Then CN.Close()
            'WB -  refreshes the list boxes
            Load_lstQualifications()
            Load_lstAllCourses()
        End If
    End Sub
    Private Sub Load_lstTeachers(ByVal strCourseId As String)

        'WB - Creates the command to load courseIDs into lstQualifications
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT TEACHER.FName, TEACHER.LName, Qualifications.Rating FROM TEACHER INNER JOIN QUALIFICATIONS on TEACHER.ID = QUALIFICATIONS.TeacherID WHERE CoursesID = @CoursesID ORDER By Rating"
        Dim Reader As SqlDataReader
        cmd.Parameters.AddWithValue("@CoursesID", strCourseId)
        lblTeachers.Text = strCourseId & " - Teachers"

        'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
        Try
            CN.Open()
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
            Me.Close()
        End Try

        'WB - Reads Teachers names  that are Qualified to teach the selected course places them into lstTeacher
        Try
            Reader = cmd.ExecuteReader
            lstTeachers.Items.Clear()
            While Reader.Read
                lstTeachers.Items.Add(Convert.ToString(Reader.Item("FName")).Trim & " " & Convert.ToString(Reader.Item("LName")).Trim)
            End While
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
        Finally
            'WB - Closes connection with the database if it is still open
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try
    End Sub
    Private Sub btnUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUp.Click
        Dim intTeacherID As Integer = getTeacherID(lstTeachers.SelectedItem.ToString.Substring(0, lstTeachers.SelectedItem.ToString.IndexOf(" ")).Trim, lstTeachers.SelectedItem.ToString.Substring(lstTeachers.SelectedItem.ToString.IndexOf(" ")).Trim)
        'WB - Creates the command to update the qualifications table
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "UPDATE QUALIFICATIONS SET Rating = @Rating WHERE CoursesID = @CoursesID AND TeacherID = @TeacherID"
        cmd.Parameters.AddWithValue("@Rating", getCurrentRating(intTeacherID, lblTeachers.Text.Substring(0, lblTeachers.Text.IndexOf(" "))) - 1)
        cmd.Parameters.AddWithValue("@CoursesID", lblTeachers.Text.Substring(0, lblTeachers.Text.IndexOf(" ")))
        cmd.Parameters.AddWithValue("@TeacherID", intTeacherID)

        'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
        Try
            CN.Open()
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
        End Try

        'WB - updates the Teachers rating
        Try
            If cmd.ExecuteNonQuery() = 0 Then
                MessageBox.Show("Did not update Teachers Rating")
            End If
        Catch ex As Exception
            MessageBox.Show("Did not update Teachers Rating" & ex.Message)
        Finally
            'WB - Closes connection with the database if it is still open
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try
        'WB - refreshes the list box
        Load_lstTeachers(lblTeachers.Text.Substring(0, lblTeachers.Text.IndexOf(" ")))
    End Sub
    Private Function getCurrentRating(ByVal intTeacherID As Integer, ByVal strQCoursesID As String) As Integer
        Dim intRating As Integer = 100

        'WB - Creates the connection to the database and creates the SQL Statement
        Dim cmd As New SqlCommand
        Dim Reader As SqlDataReader
        cmd.Connection = CN
        cmd.CommandText = "SELECT Rating FROM QUALIFICATIONS WHERE TeacherId = @TeacherID AND CoursesID = @CoursesID"
        cmd.Parameters.AddWithValue("@TeacherID", intTeacherID)
        cmd.Parameters.AddWithValue("@CoursesID", strQCoursesID)

        'WB - Tries to open the connection and produces error if it is unable to
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Cannot retreive Current Rating - " & ex.Message, "Error")
        End Try

        'WB - Executes the query and places the result in intRating
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                intRating = Convert.ToInt32(Reader.Item("Rating"))
            End While
        Catch ex As Exception
            MessageBox.Show("Cannot retreive Current Rating - " & ex.Message, "Error")
        Finally
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try

        Return intRating
    End Function
    Private Function getTeacherID(ByVal strFName As String, ByVal strLName As String) As Integer
        Dim intTeacherID As Integer

        'WB - Creates the connection to the database and creates the SQL Statement
        Dim cmd As New SqlCommand
        Dim Reader As SqlDataReader
        cmd.Connection = CN
        cmd.CommandText = "SELECT ID FROM TEACHER WHERE FName = @FName AND LName = @LName"
        cmd.Parameters.AddWithValue("@FName", strFName)
        cmd.Parameters.AddWithValue("@LName", strLName)

        'WB - Tries to open the connection and produces error if it is unable to
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Cannot retreive Current Rating - " & ex.Message, "Error")
        End Try
        'WB - Executes the query and puts the results into intTeacher to be returned.
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                intTeacherID = Convert.ToInt32(Reader.Item("ID"))
            End While
        Catch ex As Exception
            MessageBox.Show("Cannot retreive Current Rating - " & ex.Message, "Error")
        Finally
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try

        Return intTeacherID
    End Function

    Private Sub btnDown_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDown.Click
        Dim intTeacherID As Integer = getTeacherID(lstTeachers.SelectedItem.ToString.Substring(0, lstTeachers.SelectedItem.ToString.IndexOf(" ")).Trim, lstTeachers.SelectedItem.ToString.Substring(lstTeachers.SelectedItem.ToString.IndexOf(" ")).Trim)
        'WB - Creates the command to update the qualifications table
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "UPDATE QUALIFICATIONS SET Rating = @Rating WHERE CoursesID = @CoursesID AND TeacherID = @TeacherID"
        cmd.Parameters.AddWithValue("@Rating", getCurrentRating(intTeacherID, lblTeachers.Text.Substring(0, lblTeachers.Text.IndexOf(" "))) + 1)
        cmd.Parameters.AddWithValue("@CoursesID", lblTeachers.Text.Substring(0, lblTeachers.Text.IndexOf(" ")))
        cmd.Parameters.AddWithValue("@TeacherID", intTeacherID)

        'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
        Try
            CN.Open()
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
        End Try

        'WB - updates the Teachers rating
        Try
            If cmd.ExecuteNonQuery() = 0 Then
                MessageBox.Show("Did not update Teachers Rating")
            End If
        Catch ex As Exception
            MessageBox.Show("Did not update Teachers Rating" & ex.Message)
        Finally
            'WB - Closes connection with the database if it is still open
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try
        'WB - refreshes the list box
        Load_lstTeachers(lblTeachers.Text.Substring(0, lblTeachers.Text.IndexOf(" ")))
    End Sub

    Private Sub lstAllCourses_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstAllCourses.Click
        Load_lstTeachers(lstAllCourses.SelectedItem.ToString.Substring(0, lstAllCourses.SelectedItem.ToString.IndexOf(" ")))
    End Sub

    Private Sub lstQualifications_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstQualifications.Click
        Load_lstTeachers(lstQualifications.SelectedItem.ToString.Substring(0, lstQualifications.SelectedItem.ToString.IndexOf(" ")))
    End Sub
End Class